<?php

App::uses('AppModel', 'Model');

class ImportExportMonthLyDataSModel extends Model {
    public $useTable = 't_import_export_monthly_data_s';

    // Use primaryKey
    public $primaryKey = 'id';

    // 1. del_当月入出庫データＳ削除
    public function deleteImExS() {
        $this->query('TRUNCATE TABLE `t_import_export_monthly_data_s` ;');
    }


    // 2. del_当月出庫データＳ削除
    public function deleteExS() {
        $this->query('TRUNCATE TABLE `t_export_monthly_data_s` ;');
    }

    // 3. C:\石狩入出庫精算処理\入出庫データ.txt -> 当月入出庫データＳ
    public function importToImExS() {
        /*$content = file_get_contents(DATA_FILE_IMPORT_EXPORT_DIR);
        $lines  = explode("\n", $content);
        $arrDatas = array();
        foreach($lines as $line) {
            $columns = explode("\t", $lines[0]);
            $arrDatas[] = array(
                    'meisho_cd' => $columns[0],
                    'maker_cd' => $columns[1],
                    'maker_name' => $columns[2],
                    'im_ex_date' => $columns[3],
                    'category' => $columns[4],
                    'product_cd' => $columns[7],
                    'product_name' => $columns[8],
                    'area_cd' => $columns[9],
                    'area_name' => $columns[10],
                    'person_count' => $columns[11],
            );
        }
        $this->saveAll($arrDatas);
        exit();
*/
        try {
            $this->query("
                LOAD DATA INFILE '". DATA_FILE_IMPORT_EXPORT_DIR ."'
                INTO TABLE t_import_export_monthly_data_s
                CHARACTER SET sjis
                FIELDS TERMINATED BY '\t'
                OPTIONALLY ENCLOSED BY '\"'
                LINES TERMINATED BY '\r\n'
                (`meisho_cd`,`maker_cd`,`maker_name`,`im_ex_date`,`category`,`product_cd`,`product_name`,
                  @dummy, @dummy, area_cd, area_name, person_count, @dummy);");
        } catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            exit();
        }

    }
    // 4. del_当月入出庫データ削除
    public function deleteImEx() {
        $this->query('TRUNCATE TABLE `t_import_export_monthly_data` ;');
    }

    // 5. C:\石狩入出庫精算処理\出庫データ.txt -> 当月出庫データＳ
    public function importToExS() {
        try {
            $this->query("
                LOAD DATA INFILE '". DATA_FILE_EXPORT_DIR ."'
                INTO TABLE t_export_monthly_data_s
                CHARACTER SET sjis
                FIELDS TERMINATED BY '\t'
                OPTIONALLY ENCLOSED BY '\"'
                LINES TERMINATED BY '\r\n'
                (`meisho_cd`,`maker_cd`,`maker_name`,`im_ex_date`,`category`,`product_cd`,`product_name`,
                  @dummy, @dummy, area_cd, area_name, person_count, @dummy);
            ");
        } catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            exit();
        }
    }

    // 6. del_当月出庫データ削除
    public function deleteEx() {
        $this->query('TRUNCATE TABLE `t_export_monthly_data` ;');
    }

    // 7. ST_当月入出庫データ01抽出
    // 8. ST_当月入出庫データ02抽出
    // 9. ST_当月入出庫データ03（追加）
    public function miningImExData() {
        $this->query('
        INSERT INTO  t_import_export_monthly_data
        SELECT
                miningExport1.meisho_cd,
                miningExport1.maker_cd,
                miningExport1.maker_name,
                miningExport1.im_ex_date,
                miningExport1.category,
                miningExport1.product_cd,
                miningExport1.product_name,
                miningExport1.area_cd,
                miningExport1.area_name,
                miningExport1.person_count
        FROM
                (SELECT
                        t_import_export_monthly_data_s.meisho_cd,
                        t_import_export_monthly_data_s.maker_cd,
                        t_import_export_monthly_data_s.maker_name,
                        t_import_export_monthly_data_s.im_ex_date,
                        t_import_export_monthly_data_s.category,
                        t_import_export_monthly_data_s.product_cd,
                        t_import_export_monthly_data_s.product_name,
                        t_import_export_monthly_data_s.area_cd,
                        t_import_export_monthly_data_s.area_name,
                        t_import_export_monthly_data_s.person_count
                    FROM t_import_export_monthly_data_s
                    LEFT JOIN m_diff_shipper ON t_import_export_monthly_data_s.maker_cd = m_diff_shipper.shipper_cd
                    WHERE (((t_import_export_monthly_data_s.category)=6) AND ((m_diff_shipper.shipper_cd) is null))
                ) as miningExport1
        LEFT JOIN m_diff_product product ON miningExport1.product_cd = product.product_cd
        WHERE (((product.product_cd) is null))');
    }
    //10. ST2_当月出庫データ01抽出
    //11. ST2_当月出庫データ02抽出
    //12. ST2_当月出庫データ03（追加）
    public function miningExData() {
        $this->query('
                    INSERT INTO t_export_monthly_data
                    SELECT
                        miningExport1.meisho_cd,
                        miningExport1.maker_cd,
                        miningExport1.maker_name,
                        miningExport1.im_ex_date,
                        miningExport1.category,
                        miningExport1.product_cd,
                        miningExport1.product_name,
                        miningExport1.area_cd,
                        miningExport1.area_name,
                        miningExport1.person_count
                    FROM
                                (SELECT t_export_monthly_data_s.meisho_cd,
                                    t_export_monthly_data_s.maker_cd,
                                    t_export_monthly_data_s.maker_name,
                                    t_export_monthly_data_s.im_ex_date,
                                    t_export_monthly_data_s.category,
                                    t_export_monthly_data_s.product_cd,
                                    t_export_monthly_data_s.product_name,
                                    t_export_monthly_data_s.area_cd,
                                    t_export_monthly_data_s.area_name,
                                    t_export_monthly_data_s.person_count
                                    FROM t_export_monthly_data_s
                                LEFT JOIN m_diff_shipper ON t_export_monthly_data_s.maker_cd = m_diff_shipper.shipper_cd
                                WHERE (((t_export_monthly_data_s.category)=6) AND ((m_diff_shipper.shipper_cd) is null))
                                ) as miningExport1
                    LEFT JOIN m_diff_product ON miningExport1.product_cd = m_diff_product.product_cd
                    WHERE (((m_diff_product.product_cd) is null))
        ');
    }
    public function insArea() {
        $sqlArea1 = '
            (SELECT
                  t_import_export_monthly_data.area_cd,
                  t_import_export_monthly_data.area_name,
                  t_import_export_monthly_data.meisho_cd
            FROM  t_import_export_monthly_data
            WHERE ((( t_import_export_monthly_data.meisho_cd)=1))) as area1 ';
        $sqlArea2 = '
                (SELECT
                    area1.area_cd,
                    area1.area_name
                FROM '. $sqlArea1 . '
                GROUP BY area1.area_cd,
                area1.area_name) as area2 ';
         //1. ｍ_エリア生成03（エリアマスタとの不一致）
        $sqlArea3 = '
                (SELECT
                    area2.area_cd,
                    area2.area_name
                FROM '. $sqlArea2 .'
                LEFT JOIN m_area as area ON area2.area_cd = area.area_cd
                WHERE (((area.area_cd) is null))) as area3 ';
        //2. Q ｍ_エリア生成04（エリアマスタに追加）
        $this->query('
            INSERT INTO m_area ( area_cd, area_name )
            SELECT area3.area_cd, area3.area_name
            FROM '.$sqlArea3);
    }
	 //5. ｍ_荷主マスタ生成05（レコードカウント）
	 public function updateShipperSEQ() {
	 	$result = $this->query('select id from m_shipper order by shipper_cd asc');
		$i = 1;
		foreach ($result as $item){
			$this->query("update m_shipper set seq = {$i} where id = " . $item['m_shipper']['id']);
			$i++;
		}
     }
    //3. ｍ_荷主マスタ生成03（荷主マスタとの不一致）
    //4. ｍ_荷主マスタ生成04（荷主マスタに追加）
    public function insShipper() {
        $sqlShipper1 = '
            (SELECT
                t_import_export_monthly_data.maker_cd,
                t_import_export_monthly_data.maker_name
            FROM  t_import_export_monthly_data) as shipper1
        ';

        $sqlShipper2 = '
            (SELECT
                shipper1.maker_cd,
                shipper1.maker_name
            FROM '.$sqlShipper1.'
            GROUP BY
                shipper1.maker_cd,
                shipper1.maker_name) as shipper2 ';
        $sqlShipper3 = '
            (SELECT shipper2.maker_cd,
                 shipper2.maker_name
            FROM '.$sqlShipper2.'
            LEFT JOIN m_shipper as shipper
                    ON shipper2.maker_cd = shipper.shipper_cd
            WHERE (((shipper.shipper_cd) is null))) as shipper3 ';
		
        $this->query('
                INSERT INTO m_shipper
                (seq, shipper_cd, shipper_name, store_unit_price, im_ex_unit_price )
                SELECT
                	0,
                    shipper3.maker_cd,
                    shipper3.maker_name,
                    0,
                    0
                FROM ' . $sqlShipper3);
        //5. ｍ_荷主マスタ生成05（レコードカウント）
        $this->updateShipperSEQ();
        // $this->query('UPDATE m_shipper SET m_shipper.id = m_shipper.shipper_cd order by m_shipper.id desc');
		// $this->query('ALTER TABLE m_shipper ENABLE KEYS;');
        //6. m_荷主マスタ生成06（集約データ削除）
        $this->query('TRUNCATE TABLE  `t_shipper_sumary_data`');
        //7. m_荷主マスタ生成07(集約データ追加)
        $this->query(
            'INSERT INTO t_shipper_sumary_data ( shipper_cd, shipper_name )
            SELECT shipper2.maker_cd, shipper2.maker_name
            FROM ' . $sqlShipper2);
        ;
        //8. m_荷主マスタ生成08（名称置換）
        $this->query(
        'UPDATE
            m_shipper,
            t_shipper_sumary_data
        SET m_shipper.shipper_name = Replace(m_shipper.shipper_name,m_shipper.shipper_name,t_shipper_sumary_data.shipper_name)
        WHERE m_shipper.shipper_cd=t_shipper_sumary_data.shipper_cd');
    }
    public function insShippingFee() {
        $d01ExpData = '
            (SELECT
                 "出庫" AS meisho_cd,
                t_export_monthly_data.maker_cd,
                t_export_monthly_data.maker_name,
                t_export_monthly_data.im_ex_date,
                t_export_monthly_data.product_cd,
                t_export_monthly_data.product_name,
                t_export_monthly_data.area_cd,
                t_export_monthly_data.area_name,
                ceil(t_export_monthly_data.person_count) AS person_count,
                Left(Right(t_export_monthly_data.im_ex_date,4),2) AS month,
                 0*0 AS `key`
            FROM t_export_monthly_data
            WHERE t_export_monthly_data.meisho_cd=1) as d01Exp ';

        $shipFee1 = '
                (SELECT d01Exp.maker_cd, d01Exp.area_cd, 0*0 AS `unit`
                FROM ' . $d01ExpData . '
                GROUP BY d01Exp.maker_cd, d01Exp.area_cd, 0*0) as shipFee1 ';
        //9. ｍ_配送手数料生成02（配送手数料マスタとの不一致）
        $shipFee2 = '
            (SELECT
                shipFee1.maker_cd,
                shipFee1.area_cd,
                shipFee1.unit
            FROM '.$shipFee1.'
            LEFT JOIN m_shipping_fee
                ON (shipFee1.maker_cd = m_shipping_fee.shipper_cd) AND (shipFee1.area_cd = m_shipping_fee.area_cd)
            WHERE (((m_shipping_fee.shipper_cd) is null)) OR (((m_shipping_fee.area_cd) is null))) as shipFee2 ';
        // 10. ｍ_配送手数料生成03（追加）
        $this->query(
            'INSERT INTO m_shipping_fee ( shipper_cd, area_cd, delivery_fee )
            SELECT
                shipFee2.maker_cd,
                shipFee2.area_cd,
                shipFee2.unit
            FROM ' . $shipFee2);
    }
}
